# -*- encoding:utf-8 -*-
# @Date  :2019-03-01
# @Author:张璐萍（zhangluping#antiy.cn）

# 监测日志

import datetime
import json
import os
import time
import warnings

import urllib3
from elasticsearch import Elasticsearch, helpers
from flask import Blueprint, g, make_response, request, send_file
from openpyxl import Workbook
from psycopg2 import connect
from psycopg2.extras import RealDictCursor

from config import config
from lib import auth, funcs, record
from lib.validform import V, ValidateForm

urllib3.disable_warnings()
warnings.filterwarnings("ignore")

app = Blueprint(__name__ + "_app", __name__)


@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)
    g.es = Elasticsearch(
        [config.ES_ADDR],
        http_auth=(config.ES_USER_NAME, config.ES_USER_PASSWD),
        port=config.ES_PORT,
        use_ssl=True,
        ca_certs=False,
        verify_certs=False,
        timeout=config.ES_TIMEOUT,
    )


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None
    if g.es:
        g.es = None


#  数据查询
@app.route('/api/search', methods=['POST'])
@auth.permission('monitorLog', "readOnly")
def search(_currUser):
    """查询es"""
    form = ValidateForm(
        es_data=['搜索条件', V.required()],
        flag=['时间段标示', V.optional()],
        value=['值', V.optional()],
        unit=['时间单位', V.optional()],
        data_source=['数据来源', V.required()],
        startTime=['开始时间', V.optional()],
        endTime=['结束时间', V.optional()],
        aggs=['聚合字段', V.optional()],
        distinct=['聚合是否消重', V.optional()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        data['es_data'] = json.loads(data['es_data'])

        if data['aggs']:
            if int(data['distinct']) == 1:  # 消重
                if data['aggs'].split('.')[0] in config.NESTED_LIST:  # 字段类型是嵌套的
                    data['es_data']['aggs'] = {"result": {
                        "nested": {
                            "path": data['aggs'].split('.')[0] if len(data['aggs'].split('.')) != 3 else
                            data['aggs'].split('.')[0] + '.' + data['aggs'].split('.')[1]
                        },
                        "aggs": {
                            "count": {
                                "cardinality": {
                                    "field": data['aggs'],
                                    "precision_threshold": 40000
                                }
                            }
                        }
                    }}
                else:
                    data['es_data']['aggs'] = {
                        "count": {
                            "cardinality": {
                                "field": data['aggs'],
                                "precision_threshold": 40000
                            }
                        }}
            else:
                if data['aggs'].split('.')[0] in config.NESTED_LIST:  # 字段类型是嵌套的
                    data['es_data']['aggs'] = {"result": {
                        "nested": {
                            "path": data['aggs'].split('.')[0] if len(data['aggs'].split('.')) != 3 else
                            data['aggs'].split('.')[0] + '.' + data['aggs'].split('.')[1]
                        },
                        "aggs": {
                            "count": {
                                "terms": {
                                    "field": data['aggs'],
                                    "size": 9900
                                }
                            }
                        }}}
                else:
                    data['es_data']['aggs'] = {
                        "count": {
                            "terms": {
                                "field": data['aggs'],
                                "size": 9900
                            }
                        }}
        # 校验过滤字段是否存在
        if 'filter' not in data['es_data']['query']['bool']:
            data['es_data']['query']['bool']['filter'] = []
        if 'should' not in data['es_data']['query']['bool']:
            data['es_data']['query']['bool']['should'] = []
        if 'must_not' not in data['es_data']['query']['bool']:
            data['es_data']['query']['bool']['must_not'] = []

        # 判断 是 自定义时间 还是 时间周期
        if int(data['flag']) == 1:
            data['es_data']['query']['bool']['filter'].append(
                {"range": {"ts": {"gte": "now-%s%s" % (data['value'], data['unit']), "lte": "now"}}})

        else:
            data['es_data']['query']['bool']['filter'].append(
                {"range": {"ts": {"gte": data['startTime'], "lte": data['endTime']}}})

        # 切分must值
        if len(data['es_data']['query']['bool']['must']) > 0:
            line = data['es_data']['query']['bool']['must'].pop(0)['query_string']['query'].strip()

            field_logic = {"should": {}}

            if "NOT" in line:
                not_line = ''
                i = 4  # 从已知的 ( 位置 开始算起
                for character in line[4:]:
                    i = i + 1
                    if character == '(':
                        continue
                    elif character == ')':
                        break
                    else:
                        not_line = not_line + character

                line = line[i:]  # 在原字符串中去掉 NOT部分

                for item in not_line.split(" AND "):
                    item = item.strip()
                    if '<' in item or '>' in item:
                        if '<' in item and '<=' not in item:
                            field_logic = funcs.logic(item.replace('<', '>='), 'NOT', field_logic, 'gte')
                        elif '<=' in item:
                            field_logic = funcs.logic(item.replace('<=', '>'), 'NOT', field_logic, 'gt')
                        elif '>' in item and '>=' not in item:
                            field_logic = funcs.logic(item.replace('>', '<='), 'NOT', field_logic, 'lte')
                        elif '>=' in item:
                            field_logic = funcs.logic(item.replace('>=', '<'), 'NOT', field_logic, 'lt')
                    else:
                        data['es_data']['query']['bool']['must_not'].append(funcs.deal(item))

            for and_item in line.split(' AND '):
                if not and_item:  # 如果查询语句开头有NOT  后面有AND  那么会出现空格的情况
                    continue
                elif 'OR' in and_item:
                    data['es_data']['query']['bool']['minimum_should_match'] = 1
                    for or_item in and_item.split(' OR '):
                        # should 至少匹配一项
                        or_item = or_item.strip().replace('(', '').replace(')', '')
                        if '<' in or_item or '>' in or_item:
                            if '<' in or_item and '<=' not in or_item:
                                field_logic = funcs.logic(or_item, 'OR', field_logic, 'lt')
                            elif '<=' in or_item:
                                field_logic = funcs.logic(or_item, 'OR', field_logic, 'lte')
                            elif '>' in or_item and '>=' not in or_item:
                                field_logic = funcs.logic(or_item, 'OR', field_logic, 'gt')
                            elif '>=' in or_item:
                                field_logic = funcs.logic(or_item, 'OR', field_logic, 'gte')
                        else:
                            data['es_data']['query']['bool']['should'].append(funcs.deal(or_item))
                else:
                    if '<' in and_item or '>' in and_item:
                        and_item = and_item.strip()
                        if '<' in and_item and '<=' not in and_item:
                            field_logic = funcs.logic(and_item, 'AND', field_logic, 'lt')
                        elif '<=' in and_item:
                            field_logic = funcs.logic(and_item, 'AND', field_logic, 'lte')
                        elif '>' in and_item and '>=' not in and_item:
                            field_logic = funcs.logic(and_item, 'AND', field_logic, 'gt')
                        elif '>=' in and_item:
                            field_logic = funcs.logic(and_item, 'AND', field_logic, 'gte')
                    else:
                        data['es_data']['query']['bool']['must'].append(funcs.deal(and_item))

            for key, value in field_logic.items():
                if key != 'should':
                    data['es_data']['query']['bool']['filter'].append({"range": {key: value}})
                else:
                    for s_key, s_value in value.items():
                        for sc_key, sc_value in s_value.items():
                            data['es_data']['query']['bool']['should'].append(
                                {"bool": {"filter": [{"range": {s_key: {sc_key: sc_value}}}]}})
        return json.dumps({"status": "success", "data": g.es.search(index=json.loads(data['data_source']),
                                                                    body=data['es_data']), "searchLine": data['es_data']})

    except Exception, e:
        return json.dumps({"status": "fail",
                           "data": "查询失败",
                           "search": data['es_data'],
                           "line": line})


# 数据更新
@app.route('/api/search/update', methods=['POST'])
@auth.permission('monitorLog', "readOnly")
def update(_currUser):
    """更新es数据 单条"""
    form = ValidateForm(
        index=['索引', V.required()],
        type=['类型', V.required()],
        id=['id', V.required()],
        body=['需要更新的键值对', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        g.es.update(index=data['index'], doc_type=data['type'], id=data['id'], body={"doc": json.loads(data['body'])})
        rem = {
            "日志ID": data["id"],
            "更新内容": json.dumps(data["body"], ensure_ascii=False)
        }
        auth.logsync(_currUser, {"function": "监测日志", "type": "修改", "remark": "更新监测日志内容:" + record.recodelog(rem)})
        return json.dumps({"status": "success"})
    except Exception:
        return json.dumps({"status": "fail", "msg": "日志更新失败"})


# 搜索历史列表
@app.route('/api/search/history', methods=['GET'])
@auth.permission('monitorLog', "readOnly")
def history(_currUser):
    """查询历史 按照用户划分"""
    try:
        person_id = _currUser['user']['person_id']
        g.cursor.execute(
            """select se_content, d2t(max(r_time)) as time
               from h_serach_info
               where create_person = '%s'
               group by se_content order by time desc limit 10""" % person_id)
        return json.dumps({
            "status": "success",
            "data": g.cursor.fetchall()
        })
    except Exception:
        return json.dumps({"status": "fail", "msg": "查询异常"})


# 创建搜索历史
@app.route('/api/search/history', methods=['POST'])
@auth.permission('monitorLog', "readOnly")
def history_insert(_currUser):
    """插入历史"""
    form = ValidateForm(
        value=['数据json', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        person_id = _currUser['user']['person_id']
        g.cursor.execute("""insert into h_serach_info(se_content, create_person)
                            VALUES (%s, %s)""", (data['value'], person_id))
        g.conn.commit()
        return json.dumps({
            "status": "success"
        })
    except Exception:
        return json.dumps({"status": "fail", "msg": "插入异常"})


# 分析 选中单/多条数据 json格式下载
@app.route('/api/search/export/single/json', methods=['POST'])
@auth.permission('monitorLog')
def download_single_json(_currUser):
    form = ValidateForm(
        json=['原始日志', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        response = make_response(data['json'])
        response.headers["Content-Type"] = "application/octet-stream"
        response.headers["Content-Disposition"] = "attachment; filename=%s.json" % str(int(time.time()))
        return response
    except Exception:
        return json.dumps({"status": "fail", "msg": "下载数据失败"})


# 分析 选中单/多条数据 excel格式下载
@app.route('/api/search/export/single/excel', methods=['POST'])
@auth.permission('monitorLog')
def download_single_excel(_currUser):
    form = ValidateForm(
        event_type=['事件类型', V.required()],
        json=['原始日志', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        # 1.	防火墙检测(WF):          
        # 2.	WEB应用防火墙(WAF):
        # 3.	入侵检测(IDS):
        # 4.	入侵防御(IPS):
        # 5.	恶意代码检测(MALWARE):
        # 6.	C&C通讯(C2):
        # 7.	系统漏洞(OS_VUL):
        # 8.	Web应用漏洞(WEB_VUL):
        # 9.	违规外联(ILLEGAL-ACCESSS):
        # 10.	违规接入(ILLEGAL-USE):
        # 11.	系统运行状态(SYS-STATUS):
        # 12.	设备运行状态(DEV-STATUS):
        # 13.	脆弱性(VUL):
        # 14.	审计(DEV-AUDIT):
        # 15.	载荷深度分析(LOAD_ANA):
        # 16.	恶意代码传输(MALCODE_TRANSTER):

        # 创建一个工作表
        wb = Workbook()
        # 创建一个表页(sheet)
        ws = wb.active

        g.cursor.execute("""select json_object_agg(en_type_name, type_name) as stype from h_log_type""")
        stype_dict = g.cursor.fetchone()['stype']

        if data["event_type"] == "attack":
            # 表页默认名为Sheet,可重新命名
            ws.title = u"攻击日志"
            # 表头
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构",
                    u"源IP", u"目的IP", u"协议",
                    u"md5", u"恶意代码名称",
                    u"恶意代码家族", u"外联地址", u"受害主机",
                    u"传输载荷"])
            for info in json.loads(data["json"]):
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")
                if tmp_info["stype"] != '':
                    tmp_info["sip"] = info.get("sip", "")
                    tmp_info["dip"] = info.get("dip", "") if tmp_info["stype"] != 'C&C通讯' else ""
                    tmp_info["proto"] = info.get("proto", "")
                    tmp_info["md5"] = info.get("md5", "")
                    tmp_info["malname"] = info.get("malname", "")
                    tmp_info["virus_family"] = info.get("virus_family", "")
                    tmp_info["url"] = info.get("url", "") if tmp_info["stype"] != 'WEB应用防火墙' and tmp_info["stype"] != '恶意代码传输' else "" 
                    tmp_info["host"] = info.get("host", "")
                    tmp_info["load_md5"] = info.get("file", {}).get("md5") if tmp_info["stype"] == '载荷深度分析' else ""
                    _list = json.loads(config.EVENT_TYPE_SET["attack"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)

        elif data["event_type"] == "vul":
            ws.title = u"脆弱性日志"
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构",
                      u"漏洞名称", u"漏洞地址", u"漏洞编号"])

            for info in json.loads(data["json"]):
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")
                if tmp_info["stype"] != '':
                    tmp_info["vul_name"] = info.get("vul_name", "")
                    tmp_info["vul_url"] = info.get("url", "") if tmp_info["stype"] == 'Web应用漏洞' else ""
                    tmp_info["cve_id"] = info.get("cve_id", "")
                    _list = json.loads(config.EVENT_TYPE_SET["vul"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)

        elif data["event_type"] == "audit":
            ws.title = u"异常日志"
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构",
                      u"外联地址", u"设备名称", u"操作结果", u"操作时间"])
            for info in json.loads(data["json"]):
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")
                if tmp_info["stype"] != '':
                    tmp_info["url"] = info.get("url", "")
                    tmp_info["dev_name"] = info.get("dev_name", "") 
                    tmp_info["op_result"] = info.get("result", "")
                    tmp_info["op_ts"] = info.get("ts", "") if tmp_info["stype"] == '审计' else ""
                    _list = json.loads(config.EVENT_TYPE_SET["audit"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)

        elif data["event_type"] == "visible":
            ws.title = u"运行状态日志"
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构", u"可用状态"])
            for info in json.loads(data["json"]):
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")
                if tmp_info["stype"] != '':
                    if tmp_info["stype"] == '系统运行状态' or tmp_info["stype"] == '设备运行状态':
                        tmp_info["status"] = '可用' if info.get("status", "") == 1 else '不可用'
                    else:
                        tmp_info["status"] = ''
                    _list = json.loads(config.EVENT_TYPE_SET["visible"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)

        elif data["event_type"] == "all":
            ws.title = u"全部日志"
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构", u"源IP", u"目的IP", u"协议", u"md5", u"恶意代码名称", u"恶意代码家族", u"外联地址", u"受害主机", u"传输载荷", u"漏洞名称", u"漏洞地址", u"漏洞编号", u"设备名称", u"操作结果", u"操作时间", u"在用状态"])
            for info in json.loads(data["json"]):
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")      
                if tmp_info["stype"] != '':
                    if tmp_info["stype"] == '系统运行状态' or tmp_info["stype"] == '设备运行状态':
                        tmp_info["status"] = '可用' if info.get("status", "") == 1 else '不可用'
                    else:
                        tmp_info["status"] = ''
                    tmp_info["sip"] = info.get("sip", "")
                    tmp_info["dip"] = info.get("dip", "") if tmp_info["stype"] != 'C&C通讯' else ""
                    tmp_info["proto"] = info.get("proto", "")
                    tmp_info["md5"] = info.get("md5", "") 
                    tmp_info["malname"] = info.get("malname", "")
                    tmp_info["virus_family"] = info.get("virus_family", "")
                    tmp_info["url"] = info.get("url", "") if tmp_info["stype"] != 'Web应用漏洞' and tmp_info["stype"] != 'WEB应用防火墙' and tmp_info["stype"] != '恶意代码传输' else ""
                    tmp_info["host"] = info.get("host", "")
                    tmp_info["load_md5"] = info.get("file", {}).get("md5") if tmp_info["stype"] == '载荷深度分析' else ""
                    tmp_info["vul_name"] = info.get("vul_name", "")
                    tmp_info["vul_url"] = info.get("url", "") if tmp_info["stype"] == 'Web应用漏洞' else ""
                    tmp_info["cve_id"] = info.get("cve_id", "")
                    tmp_info["dev_name"] = info.get("dev_name", "")
                    tmp_info["op_result"] = info.get("result", "")
                    tmp_info["op_ts"] = info.get("ts", "") if tmp_info["stype"] == '审计' else ""
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)
        if os.path.exists(config.EXPORT_EVENTS_SAVE) is False:  # 判断路径是否存在
            # 创建路径
            os.makedirs(config.EXPORT_EVENTS_SAVE)
        filepath = os.path.join(config.EXPORT_EVENTS_SAVE) + "/" + data["event_type"] + '.xlsx'
        wb.save(filepath)
        response = make_response(send_file(filepath))
        response.headers["Content-Disposition"] = "attachment; filename=%s;" % (data["event_type"] + '_' + str(int(time.time())) + '.xlsx')
        return response
    except Exception:
        return json.dumps({"status": "fail", "msg": "导出失败"})


# 监测日志 批量导出(10000条)
@app.route('/api/search/export/events/json', methods=['POST'])
@auth.permission('monitorLog')
def export_all_as_json(_currUser):
    form = ValidateForm(
        searchLine=['数据json', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    data["searchLine"] = eval(data["searchLine"])
    data["searchLine"]["size"] = 9999
    try:
        result = g.es.search(index=["AIR"], body=data["searchLine"])
        if os.path.exists(config.EXPORT_EVENTS_SAVE) is False:  # 判断路径是否存在
            # 创建路径
            os.makedirs(config.EXPORT_EVENTS_SAVE)
        with open(os.path.join(config.EXPORT_EVENTS_SAVE, 'test.json'), 'wb') as f:
            f.write(json.dumps(result['hits']['hits']))
        response = make_response(send_file(os.path.join(config.EXPORT_EVENTS_SAVE, 'test.json')))
        response.headers["Content-Disposition"] = "attachment; filename=%s.json" % str(int(time.time()))
        return response
    except Exception:
        return json.dumps({"status": "fail", "msg": "导出失败"})


# 监测日志 批量导出(10000条)
@app.route('/api/search/export/events/excel', methods=['POST'])
@auth.permission('monitorLog')
def export_all_as_excel(_currUser):
    form = ValidateForm(
        searchLine=['搜索条件', V.required()],
        event_type=['数据种类', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    data["searchLine"] = eval(data["searchLine"])
    data["searchLine"]["size"] = 9999

    try:
        result = g.es.search(index=["AIR"], body=data["searchLine"])
        if len(result["hits"]["hits"]) == 0:
            return json.dumps({"status": "fail", "msg": "导出结果为空"})

        wb = Workbook()
        ws = wb.active

        g.cursor.execute("""select json_object_agg(en_type_name, type_name) as stype from h_log_type""")
        stype_dict = g.cursor.fetchone()['stype']

        if data["event_type"] == "attack":
            # 表页默认名为Sheet,可重新命名
            ws.title = u"攻击日志"
            # 表头
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构",
                      u"源IP", u"目的IP", u"协议", u"md5", u"恶意代码名称", u"恶意代码家族", u"外联地址", u"受害主机", u"传输载荷"])
            for _info in result["hits"]["hits"]:
                info = _info["_source"]
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")

                if tmp_info["stype"] != '':
                    tmp_info["sip"] = info.get("sip", "")
                    tmp_info["dip"] = info.get("dip", "") if tmp_info["stype"] != 'C&C通讯' else ""
                    tmp_info["proto"] = info.get("proto", "")
                    tmp_info["md5"] = info.get("md5", "")
                    tmp_info["malname"] = info.get("malname", "")
                    tmp_info["virus_family"] = info.get("virus_family", "")
                    tmp_info["url"] = info.get("url", "") if tmp_info["stype"] != 'WEB应用防火墙' and tmp_info["stype"] != '恶意代码传输' else "" 
                    tmp_info["host"] = info.get("host", "")
                    tmp_info["load_md5"] = info.get("file", {}).get("md5") if tmp_info["stype"] == '载荷深度分析' else ""
                    _list = json.loads(config.EVENT_TYPE_SET["attack"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)

        elif data["event_type"] == "vul":
            ws.title = u"脆弱性日志"
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构",
                      u"漏洞名称", u"漏洞地址", u"漏洞编号"])

            for _info in result["hits"]["hits"]:
                info = _info["_source"]
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")

                if tmp_info["stype"] != '':
                    tmp_info["vul_name"] = info.get("vul_name", "")
                    tmp_info["vul_url"] = info.get("url", "") if tmp_info["stype"] == 'Web应用漏洞' else ""
                    tmp_info["cve_id"] = info.get("cve_id", "")
                    _list = json.loads(config.EVENT_TYPE_SET["vul"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)

        elif data["event_type"] == "audit":
            ws.title = u"异常日志"
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构",
                      u"外联地址", u"设备名称", u"操作结果", u"操作时间"])
            for _info in result["hits"]["hits"]:
                info = _info["_source"]
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")

                if tmp_info["stype"] != '':
                    tmp_info["url"] = info.get("url", "")
                    tmp_info["dev_name"] = info.get("dev_name", "") 
                    tmp_info["op_result"] = info.get("result", "")
                    tmp_info["op_ts"] = info.get("ts", "") if tmp_info["stype"] == '审计' else ""
                    _list = json.loads(config.EVENT_TYPE_SET["audit"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)

        elif data["event_type"] == "visible":
            ws.title = u"运行状态日志"
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构", u"可用状态"])
            for _info in result["hits"]["hits"]:
                info = _info["_source"]
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")

                if tmp_info["stype"] != '':
                    if tmp_info["stype"] == '系统运行状态' or tmp_info["stype"] == '设备运行状态':
                        tmp_info["status"] = '可用' if info.get("status", "") == 1 else '不可用'
                    else:
                        tmp_info["status"] = ''
                    _list = json.loads(config.EVENT_TYPE_SET["visible"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)

        elif data["event_type"] == "all":
            ws.title = u"全部日志"
            ws.append([u"发生时间", u"日志ID", u"日志类型", u"系统名称", u"所属机构", u"源IP", u"目的IP", u"协议", u"md5", u"恶意代码名称", u"恶意代码家族", u"外联地址", u"受害主机", u"传输载荷", u"漏洞名称", u"漏洞地址", u"漏洞编号", u"设备名称", u"操作结果", u"操作时间", u"在用状态"])
            for _info in result["hits"]["hits"]:
                info = _info["_source"]
                tmp_info = funcs.get_events_info(info)
                tmp_info["stype"] = stype_dict.get(tmp_info["stype"], "")

                if tmp_info["stype"] != '':
                    if tmp_info["stype"] == '系统运行状态' or tmp_info["stype"] == '设备运行状态':
                        tmp_info["status"] = '可用' if info.get("status", "") == 1 else '不可用'
                    else:
                        tmp_info["status"] = ''
                    tmp_info["sip"] = info.get("sip", "")
                    tmp_info["dip"] = info.get("dip", "") if tmp_info["stype"] != 'C&C通讯' else ""
                    tmp_info["proto"] = info.get("proto", "")
                    tmp_info["md5"] = info.get("md5", "") 
                    tmp_info["malname"] = info.get("malname", "")
                    tmp_info["virus_family"] = info.get("virus_family", "")
                    tmp_info["url"] = info.get("url", "") if tmp_info["stype"] != 'Web应用漏洞' and tmp_info["stype"] != 'WEB应用防火墙' and tmp_info["stype"] != '恶意代码传输' else ""
                    tmp_info["host"] = info.get("host", "")
                    tmp_info["load_md5"] = info.get("file", {}).get("md5") if tmp_info["stype"] == '载荷深度分析' else ""
                    tmp_info["vul_name"] = info.get("vul_name", "")
                    tmp_info["vul_url"] = info.get("url", "") if tmp_info["stype"] == 'Web应用漏洞' else ""
                    tmp_info["cve_id"] = info.get("cve_id", "")
                    tmp_info["dev_name"] = info.get("dev_name", "")
                    tmp_info["op_result"] = info.get("result", "")
                    tmp_info["op_ts"] = info.get("ts", "") if tmp_info["stype"] == '审计' else ""
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                else:
                    _list = json.loads(config.EVENT_TYPE_SET["all"] % tmp_info)
                ws.append(_list)
        if os.path.exists(config.EXPORT_EVENTS_SAVE) is False:  # 判断路径是否存在
            # 创建路径
            os.makedirs(config.EXPORT_EVENTS_SAVE)
        filepath = os.path.join(config.EXPORT_EVENTS_SAVE) + "/" + data["event_type"] + '.xlsx'
        wb.save(filepath)
        response = make_response(send_file(filepath))
        response.headers["Content-Disposition"] = "attachment; filename=%s;" % (data["event_type"] + '_' + str(int(time.time())) + '.xlsx')
        return response

    except Exception:
        return json.dumps({"status": "fail", "msg": "导出失败"})


# 监测日志 聚合结果json格式下载
@app.route('/api/search/export/aggs/json', methods=['POST'])
@auth.permission('monitorLog')
def aggs_as_json(_currUser):
    form = ValidateForm(
        name=['数据种类', V.required()],
        data=['数据json', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    search_line = eval(data["data"])
    search_line["size"] = 0
    search_line['aggs']['groupData']['terms']['size'] = 9900
    try:
        """
            数据种类 值&含义 对照
            stype -> 日志类型 | name -> 系统名称 | belong_area -> 所属区域
            unit -> 所属机构
        """
        result = g.es.search(index='AIR', body=search_line)["aggregations"]["groupData"]["buckets"]
        # 日志类型 英文转中文
        if data["name"] == 'stype':
            g.cursor.execute("""select json_object_agg(en_type_name, type_name) as stype from h_log_type""")
            stype = g.cursor.fetchone()
            _result = []
            for item in result:
                item['key'] = stype["stype"].get(item["key"], item["key"])
                _result.append(item)
        else:
            _result = result
        response = make_response(json.dumps(_result, ensure_ascii=False))
        response.headers["Content-Disposition"] = "attachment; filename=%s.json" % \
                                                  (config.EVENTS_AGGS_TYPE.get(data["name"])["name"] + str(
                                                      int(time.time()))).encode("utf-8")
        return response
    except Exception:
        return json.dumps({"status": "fail", "msg": "下载失败"})


# 监测日志 日志来源筛选栏(所属区域、严重级别)
@app.route('/api/search/log/filter', methods=["POST"])
@auth.permission('monitorLog', "readOnly")
def filter_log(_currUser):
    form = ValidateForm(
        # attack、vul、visible、audit
        type=['日志类别', V.optional()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    try:
        # 所属区域
        g.cursor.execute("""select distinct area_name from h_area_info""")
        log_area = g.cursor.fetchall()

        return json.dumps({
            "status": "success",
            "data": {
                "log_area": [v["area_name"] for v in log_area],
                "level": ["1", "2", "3", "4"]
            }
        })
    except Exception:
        return json.dumps(
            {
                "status": "fail",
                "msg": "筛选条件加载失败"
            }
        )


# 日志类型 中英文对照
@app.route('/api/search/log/filter/stypedict', methods=["POST"])
@auth.permission('monitorLog', "readOnly")
def filter_log_stype_dict(_currUser):
    form = ValidateForm(
        # attack、vul、visible、audit
        type=['日志类别', V.optional()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    try:
        # 获取小日志类型的中文和英文
        g.cursor.execute("""select json_object_agg(en_type_name, type_name) as stype from h_log_type""")
        stype_dict = g.cursor.fetchone()
        return json.dumps({
            "status": "success",
            "data": stype_dict["stype"]

        })
    except Exception:
        return json.dumps(
            {
                "status": "fail",
                "msg": "日志类型中英文对照加载失败"
            }
        )


# 监测日志 筛选条件 日志类型
@app.route('/api/search/log/filter/stype', methods=["POST"])
@auth.permission('monitorLog', "readOnly")
def filter_stype(_currUser):
    form = ValidateForm(
        # attack、vul、visible、audit
        type=['日志类别', V.optional()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    try:
        # 小的日志类型 如入侵检测
        sql = """select en_type_name
                        from h_log_type"""
        if data["type"] != 'all':
            sql += """ where log_type = '%s' """ % data["type"]
        sql += " order by log_type asc, type_id asc"
        g.cursor.execute(sql)
        stype = g.cursor.fetchall()

        return json.dumps({
            "status": "success",
            "data": [v["en_type_name"] for v in stype]
        })
    except Exception:
        return json.dumps(
            {
                "status": "fail",
                "msg": "日志类型加载失败"
            }
        )


# 监测日志 日志来源 筛选 所属机构
@app.route('/api/search/log/filter/affiliation', methods=["POST"])
@auth.permission('monitorLog', "readOnly")
def filter_affiliation(_currUser):
    form = ValidateForm(
        area=['所属区域', V.optional()],
        location=['区域所在地理位置', V.optional()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    try:

        sql = """select DISTINCT trim(a.agency_name) as agency_name
                 from sys_agency_info a where a.node_type_id > 0 and a.state = '1' 
                 and trim(a.agency_name) !='默认节点' and a.agency_name !=''"""

        if data["area"]:
            sql += " and a.belong_area = '%s' " % data["area"]

        if data["location"]:
            i = 0
            for x in json.loads(data["location"]):
                if len(x) > 0 and i == 0:
                    sql += " and a.province = '%s' " % x
                if len(x) > 0 and i == 1:
                    sql += " and a.city = '%s' " % x
                if len(x) > 0 and i == 2:
                    sql += " and a.area = '%s' " % x
                i += 1
        g.cursor.execute(sql)
        result = g.cursor.fetchall()
        _result = []
        if len(result) > 0:
            _result = [v["agency_name"].strip() for v in result]
        return json.dumps(
            {
                "status": "success",
                "data": _result
            }
        )
    except Exception:
        return json.dumps(
            {
                "status": "fail",
                "msg": "所属机构列表获取失败"
            }
        )


# 监测日志 日志来源 筛选 系统名称
@app.route('/api/search/log/filter/sysname', methods=["POST"])
@auth.permission('monitorLog', "readOnly")
def filter_system_name(_currUser):
    form = ValidateForm(
        bagency_name=['所属机构', V.optional()],
        bbelong_area=['机构所在区域', V.optional()],
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)
    try:
        sql = """select DISTINCT trim(a.info_sys_name) as sys_name
                  from h_information_system a
                  left join sys_agency_info b on a.agency_id = b.agency_id
                  where a.state = '1' and a.info_sys_name !='' """
        for k, v in data.items():
            if v:
                # b.agency_name = bagency_name
                sql += """and %s = '%s'""" % (k[:1] + '.' + k[1:], v)
        g.cursor.execute(sql)
        result = g.cursor.fetchall()
        _result = []
        if len(result) > 0:
            _result = [v["sys_name"].strip() for v in result]
        return json.dumps(
            {
                "status": "success",
                "data": _result
            }
        )
    except Exception:
        return json.dumps(
            {
                "status": "fail",
                "msg": "系统名称查询失败"
            }
        )


# 省份 城市 三层结构
@app.route('/api/search/area/list', methods=["POST"])
@auth.permission('monitorLog', "readOnly")
def area_list():
    form = ValidateForm(
        area_name=['地区名字', V.optional()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    try:
        sql = """select a.province_name, a.city_name, a.district_name, b.area_name
                 from h_geography_info a
                 left join h_area_info b on a.area_id = b.area_id
                 where a.province_name is not null"""
        if data["area_name"]:
            sql = sql + """ and b.area_name = '%s'""" % data["area_name"]
        g.cursor.execute(sql)
        areas = g.cursor.fetchall()
        tmp_result = {}
        result = []

        for area in areas:
            # 判断省份是否存在
            if area["province_name"] not in tmp_result:
                tmp_result[area["province_name"]] = {}

            # 判断城市是否存在
            if area["city_name"] not in tmp_result[area["province_name"]]:
                tmp_result[area["province_name"]][area["city_name"]] = []

            tmp_result[area["province_name"]][area["city_name"]].append({"value": area["district_name"],
                                                                        "label": area["district_name"]})

        # 按照前端要求构造结构
        for f_key, f_value in tmp_result.items():
            f_result = {
                "value": f_key,
                "label": f_key,
                "children": []
            }
            for key, value in f_value.items():
                f_result["children"].append(
                    {
                        "value": key,
                        "label": key,
                        "children": value
                    }
                )

            result.append(f_result)
        return json.dumps(
            {
                "status": "success",
                "data": result
            }
        )
    except Exception:
        return json.dumps(
            {
                "status": "fail",
                "msg": "省/市/区三层结构"
            }
        )


# 根据ID查询数据
@app.route('/api/search/id', methods=["POST"])
@auth.permission('monitorLog', "readOnly")
def search_id():
    form = ValidateForm(
        id=['日志ID', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    search_data = {
        "size": 1,
        "query": {
            "bool": {
                "must": [
                    {
                        "query_string": {
                            "query": "_id:" + data["id"]
                        }
                    }
                ]
            }
        }
    }

    return json.dumps(
        {
            "status": "success",
            "data": g.es.search(index=["AIR"], body=search_data)
        }
    )
